﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using NewDDSys.IDAL;
using NewDDSys.Entity;
using NewDDSys.Utility;
using System.Text;

namespace NewDDSys.ORADAL
{
    public class YsjhDAL:IYsjhDAL
    {
        public int AddYsjh(YsjhInfo ys)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, ys.Num),
                DALHelper.MakeInParam(":yday", OracleType.VarChar, ys.Yday),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ys.Ku),
                DALHelper.MakeInParam(":pinzhong", OracleType.VarChar, ys.Pinzhong),
                DALHelper.MakeInParam(":cs", OracleType.Number, ys.Cs),
                DALHelper.MakeInParam(":xday", OracleType.VarChar, ys.Xday)
            };
            return (DALHelper.RunCommand(@"insert into dd.ysjh values (:num, to_date(:yday,'yyyy-mm-dd'), :ku, :pinzhong, :cs, to_date(:xday,'yyyy-mm-dd'))", prams));
        }
        public int UpdateYsjh(string num, string yday, string ku, string pinzhong, int cs)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num),
                
                DALHelper.MakeInParam(":yday", OracleType.VarChar, yday),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":pinzhong", OracleType.VarChar, pinzhong),
                DALHelper.MakeInParam(":cs", OracleType.Number, cs)
            };
            return (DALHelper.RunCommand(@"update dd.ysjh set yday = to_date(:yday,'yyyy-mm-dd'), ku = :ku, pinzhong = :pinzhong, cs = :cs where num = :num", prams));
        }
        public int Del(string num)
        {
            OracleParameter[] prams = {
                
                DALHelper.MakeInParam(":num", OracleType.VarChar, num)                      
            };
            return (DALHelper.RunCommand(@"delete from dd.ysjh where NUM = :num", prams));
        }
        public DataSet Select(string num)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num)
            };
            return (DALHelper.RunProcReturn(@"select * from dd.ysjh where NUM = :num", prams, "Sbynum"));
        }
        public DataSet SelectByDay(string sday)
        {
            
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":sday", OracleType.VarChar, sday)
            };

            return (DALHelper.RunProcReturn(@"select * from dd.ysjh where xday = to_date(:sday,'yyyy-mm-dd')", prams, "Sbyday"));
        
        }
        public string ysr(string cday, string ku)
        {
            OracleParameter[] prams = {
                
                DALHelper.MakeInParam(":cday", OracleType.VarChar, cday),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(cs) as jhcs from dd.ysjh where yday = to_date(:cday,'yyyy-mm-dd') and ku = :ku", prams);
            return obj.ToString();
        }
        public string AUTOID()
        {

            string num = "";
            object time = DALHelper.ExecuteSQLScalar(@"select sysdate from dual");
            num = "ysjh" + time.ToString();


            return num;
        }
        public string RHJ(string cday)
        {
            OracleParameter[] prams = {
                
                DALHelper.MakeInParam(":cday", OracleType.VarChar, cday)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(cs) as jhcs from dd.ysjh where yday = to_date(:cday,'yyyy-mm-dd')", prams);
            if (obj == null)
                return "";
            else
                return obj.ToString();
            
            
        }
        public string JDXC()
        {
            string cday = DateTime.Now.AddDays(-1).ToShortDateString().ToString();
            OracleParameter[] prams = {
                
                DALHelper.MakeInParam(":cday", OracleType.VarChar, cday)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(cs) from dd.ysjh where yday = to_date(:cday,'yyyy-mm-dd') and ku = '嘉东库' and pinzhong in ('普碳线材', '品种线材')", prams);
            if (obj == null)
                return "";
            else
                return obj.ToString();
            
        }
        public string JDBC()
        {
            string cday = DateTime.Now.AddDays(-1).ToShortDateString().ToString();
            OracleParameter[] prams = {
                
                DALHelper.MakeInParam(":cday", OracleType.VarChar, cday)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(cs) from dd.ysjh where yday = to_date(:cday,'yyyy-mm-dd') and ku = '嘉东库' and pinzhong in ('棒材')", prams);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }
        public string JDJB()
        {
            string cday = DateTime.Now.AddDays(-1).ToShortDateString().ToString();
            OracleParameter[] prams = {
                
                DALHelper.MakeInParam(":cday", OracleType.VarChar, cday)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(cs) from dd.ysjh where yday = to_date(:cday,'yyyy-mm-dd') and ku = '嘉东库' and pinzhong in ('碳钢热轧卷板', '不锈钢热轧卷板')", prams);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }
        public string JDZB()
        {
            string cday = DateTime.Now.AddDays(-1).ToShortDateString().ToString();
            OracleParameter[] prams = {
                
                DALHelper.MakeInParam(":cday", OracleType.VarChar, cday)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(cs) from dd.ysjh where yday = to_date(:cday,'yyyy-mm-dd') and ku = '嘉东库' and pinzhong in ('板材')", prams);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }
        public string YHJ(string kday, string jday)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":kday", OracleType.VarChar, kday),
                DALHelper.MakeInParam(":jday", OracleType.VarChar, jday)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(cs) from dd.ysjh where yday >= to_date(:kday,'yyyy-mm-dd') and yday <= to_date(:jday,'yyyy-mm-dd')", prams);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }

    }
}
